--    Author    : MAYANTING
--    Name      : ADM.ANA_SUST_DLMG_RATE_LOAN_RATE.HQL
--    Functions :
--    Purpose   : Daily saving the variation data from stg
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-05-24  MAYANTING        1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE ADM.ANA_SUST_DLMG_RATE_LOAN_RATE PARTITION(DATA_DATE = '20180331')
   SELECT
           SUBSTR('20180331',1,6)                    AS DATA_MONTH
          ,T.ORG_CODE_1                                   AS ORG_CODE_1
          ,T.ORG_DSCR_1                                   AS ORG_DSCR_1
          ,T.ORG_CODE_2                                   AS ORG_CODE_2
          ,T.ORG_DSCR_2                                   AS ORG_DSCR_2
          ,T.ORG_CODE_3                                   AS ORG_CODE_3
          ,T.ORG_DSCR_3                                   AS ORG_DSCR_3
          ,T.AREA_CODE_1                                  AS AREA_CODE_1
          ,T.AREA_DSCR_1                                  AS AREA_DSCR_1
          ,T.AREA_CODE_2                                  AS AREA_CODE_2
          ,T.AREA_DSCR_2                                  AS AREA_DSCR_2
          ,T.AREA_CODE_3                                  AS AREA_CODE_3
          ,T.AREA_DSCR_3                                  AS AREA_DSCR_3
          ,T.CUSTOMER_TYPE                                AS CUSTOMER_TYPE            --  客户类型 单位客户=0；个人客户=1
          ,T.PRODUCT_CATEGORY                             AS PRODUCT_CATEGORY         --  贷款产品类别  展示一级
          ,T.LPIC                                         AS LPIC                     --  企业分行业(贷款主体行业类别)
          ,T.LOANS_ACTUALLY                               AS LOANS_ACTUALLY           --  行业投向  展示一级
          ,T.ENTERPRISE_SCALE                             AS ENTERPRISE_SCALE         --  企业规模 大、中、小、微型企业分别对应代码：CS01、CS02、CS03、CS04 非企业类单位对应代码：CS05。
          ,T.LOAN_PERIOD                                  AS LOAN_PERIOD              --  贷款期限 1：6个月（含）以内 2：6个月至1年（含） 3：1至3年（含） 4：3至5年（含）5：5至10年（含）6：10年以上 7：未定期限"
          ,T.ENTERPRISE_INVESTOR                          AS ENTERPRISE_INVESTOR      -- 企业出资人经济成分 展示二级
          ,T.GUARANTY_STYLE                               AS GUARANTY_STYLE           -- 担保方式
          ,T.LOAN_QUALITY                                 AS LOAN_QUALITY             -- 贷款质量  FQ01-正常类贷款 FQ02-关注类贷款 FQ03-次级类贷款 FQ04-可疑类贷款 FQ05-损失类贷款
          ,T.LOAN_STATUS                                  AS LOAN_STATUS              -- 贷款状态 FS01-正常 FS02-展期 FS03-逾期 FS09-缩期 ...
          ,T.CCY                                          AS CCY                      -- 币种
          -- 处理单位为亿元
          ,SUM(T.LOAN_BAL        )/100000000              AS LOAN_BAL
          ,SUM(T.LOAN_BAL_LM     )/100000000              AS LOAN_BAL_LM
          ,SUM(T.LOAN_BAL_LY     )/100000000              AS LOAN_BAL_LY
          ,SUM(T.WSUM_BAL        )/100000000              AS WSUM_BAL
          ,SUM(T.WSUM_BAL_LM     )/100000000              AS WSUM_BAL_LM
          ,SUM(T.WSUM_BAL_LY     )/100000000              AS WSUM_BAL_LY
          ,SUM(T.RATE_SUM_BAL    )/100000000              AS RATE_SUM_BAL
          ,SUM(T.RATE_SUM_BAL_LM )/100000000              AS RATE_SUM_BAL_LM
          ,SUM(T.RATE_SUM_BAL_LY )/100000000              AS RATE_SUM_BAL_LY
          ,SUM(T.BAL_COUNT       )                        AS BAL_COUNT
          ,SUM(T.BAL_COUNT_LM    )                        AS BAL_COUNT_LM
          ,SUM(T.BAL_COUNT_LY    )                        AS BAL_COUNT_LY
          ,SUM(T.LOAN_AMT        )/100000000              AS LOAN_AMT
          ,SUM(T.LOAN_AMT_LM     )/100000000              AS LOAN_AMT_LM
          ,SUM(T.LOAN_AMT_LY     )/100000000              AS LOAN_AMT_LY
          ,SUM(T.WSUM_AMT        )/100000000              AS WSUM_AMT
          ,SUM(T.WSUM_AMT_LM     )/100000000              AS WSUM_AMT_LM
          ,SUM(T.WSUM_AMT_LY     )/100000000              AS WSUM_AMT_LY
          ,SUM(T.RATE_SUM_AMT    )/100000000              AS RATE_SUM_AMT
          ,SUM(T.RATE_SUM_AMT_LM )/100000000              AS RATE_SUM_AMT_LM
          ,SUM(T.RATE_SUM_AMT_LY )/100000000              AS RATE_SUM_AMT_LY
          ,SUM(T.AMT_COUNT       )                        AS AMT_COUNT
          ,SUM(T.AMT_COUNT_LM    )                        AS AMT_COUNT_LM
          ,SUM(T.AMT_COUNT_LY    )                        AS AMT_COUNT_LY
    FROM
        (       SELECT   ORG.ORG_CODE_1                     AS ORG_CODE_1
                        ,ORG.ORG_DSCR_1                     AS ORG_DSCR_1
                        ,ORG.ORG_CODE_2                     AS ORG_CODE_2
                        ,ORG.ORG_DSCR_2                     AS ORG_DSCR_2
                        ,ORG.ORG_CODE_3                     AS ORG_CODE_3
                        ,ORG.ORG_DSCR_3                     AS ORG_DSCR_3
                        ,AREA.AREA_CODE_1                   AS AREA_CODE_1
                        ,AREA.AREA_DSCR_1                   AS AREA_DSCR_1
                        ,AREA.AREA_CODE_3                   AS AREA_CODE_2
                        ,AREA.AREA_DSCR_3                   AS AREA_DSCR_2
                        ,AREA.AREA_CODE_4                   AS AREA_CODE_3
                        ,AREA.AREA_DSCR_4                   AS AREA_DSCR_3
                        ,YE.CLIENT_TYPE                     AS CUSTOMER_TYPE        --  客户类型 单位客户=0；个人客户=1
                        ,PRO.LOAN_PRODUCT_ID                AS PRODUCT_CATEGORY     --  贷款产品类别  展示一级
                        ,YE.CLIENT_INDUSTRY                 AS LPIC                 --  企业分行业(贷款主体行业类别)
                        ,ACTU.INDUSTRY_CODE_1               AS LOANS_ACTUALLY       --  行业投向  展示一级
                        ,YE.ENTERPRISE_SCALE                AS ENTERPRISE_SCALE     --  企业规模 大、中、小、微型企业分别对应代码：CS01、CS02、CS03、CS04 非企业类单位对应代码：CS05。
                        ,YE.TERM_CODE                       AS LOAN_PERIOD          --  贷款期限 1：6个月（含）以内 2：6个月至1年（含） 3：1至3年（含） 4：3至5年（含）5：5至10年（含）6：10年以上 7：未定期限"
                        ,ECO.ECO_SEC_CAT_ID_2               AS ENTERPRISE_INVESTOR  -- 企业出资人经济成分 展示二级
                        ,GUAR.LOAN_GUAR_TYPE_ID_1           AS GUARANTY_STYLE       -- 担保方式   展示一级
                        ,YE.FIVE_CLASS                      AS LOAN_QUALITY         -- 贷款质量  FQ01-正常类贷款 FQ02-关注类贷款 FQ03-次级类贷款 FQ04-可疑类贷款 FQ05-损失类贷款
                        ,YE.LOAN_STATUS                     AS LOAN_STATUS          -- 贷款状态 FS01-正常 FS02-展期 FS03-逾期 FS09-缩期 ...
                        ,YE.CCY                             AS CCY                  -- 币种
                        ,CASE WHEN YE.DATA_DATE='20180331'       THEN COALESCE(YE.ACTUAL_BAL,0) ELSE 0    END    AS LOAN_BAL                -- 当月贷款余额
                        ,CASE WHEN YE.DATA_DATE='20180228' THEN COALESCE(YE.ACTUAL_BAL,0) ELSE 0    END    AS LOAN_BAL_LM             -- 上月贷款余额
                        ,CASE WHEN YE.DATA_DATE='20170331'  THEN COALESCE(YE.ACTUAL_BAL,0) ELSE 0    END    AS LOAN_BAL_LY             -- 去年同期贷款余额
                        ,CASE WHEN YE.DATA_DATE='20180331'       THEN COALESCE(YE.WSUM_BAL,0)   ELSE 0    END    AS WSUM_BAL                -- 当月余额加权金额
                        ,CASE WHEN YE.DATA_DATE='20180228' THEN COALESCE(YE.WSUM_BAL,0)   ELSE 0    END    AS WSUM_BAL_LM             -- 上月余额加权金额
                        ,CASE WHEN YE.DATA_DATE='20170331'  THEN COALESCE(YE.WSUM_BAL,0)   ELSE 0    END    AS WSUM_BAL_LY             -- 去年同期余额加权金额
                        ,CASE WHEN YE.DATA_DATE='20180331'       THEN COALESCE(YE.RATE,0)       ELSE 0    END    AS RATE_SUM_BAL            -- 当月贷款余额利率合计
                        ,CASE WHEN YE.DATA_DATE='20180228' THEN COALESCE(YE.RATE,0)       ELSE 0    END    AS RATE_SUM_BAL_LM         -- 上月贷款余额利率合计
                        ,CASE WHEN YE.DATA_DATE='20170331'  THEN COALESCE(YE.RATE,0)       ELSE 0    END    AS RATE_SUM_BAL_LY         -- 去年同期余额利率合计
                        ,CASE WHEN YE.DATA_DATE='20180331'       THEN 1                                   END    AS BAL_COUNT               -- 当月贷款余额笔数
                        ,CASE WHEN YE.DATA_DATE='20180228' THEN 1                                   END    AS BAL_COUNT_LM            -- 上月贷款余额笔数
                        ,CASE WHEN YE.DATA_DATE='20170331'  THEN 1                                   END    AS BAL_COUNT_LY            -- 去年同期贷款余额笔数
                        ,CASE WHEN FE.DATA_DATE='20180331'       THEN COALESCE(FE.OCCUR_AMOUNT,0)ELSE 0   END    AS LOAN_AMT                -- 当月贷款发生额
                        ,CASE WHEN FE.DATA_DATE='20180228' THEN COALESCE(FE.OCCUR_AMOUNT,0)ELSE 0   END    AS LOAN_AMT_LM             -- 上月贷款发生额
                        ,CASE WHEN FE.DATA_DATE='20170331'  THEN COALESCE(FE.OCCUR_AMOUNT,0)ELSE 0   END    AS LOAN_AMT_LY             -- 去年同期贷款发生额
                        ,CASE WHEN FE.DATA_DATE='20180331'       THEN COALESCE(FE.WSUM_AMOUNT,0) ELSE 0   END    AS WSUM_AMT                -- 当月发生额加权金额
                        ,CASE WHEN FE.DATA_DATE='20180228' THEN COALESCE(FE.WSUM_AMOUNT,0) ELSE 0   END    AS WSUM_AMT_LM             -- 上月发生额加权金额
                        ,CASE WHEN FE.DATA_DATE='20170331'  THEN COALESCE(FE.WSUM_AMOUNT,0) ELSE 0   END    AS WSUM_AMT_LY             -- 去年同期发生额加权金额
                        ,CASE WHEN FE.DATA_DATE='20180331'       THEN COALESCE(FE.RATE,0)        ELSE 0   END    AS RATE_SUM_AMT            -- 当月贷款发生额利率合计
                        ,CASE WHEN FE.DATA_DATE='20180228' THEN COALESCE(FE.RATE,0)        ELSE 0   END    AS RATE_SUM_AMT_LM         -- 上月贷款发生额利率合计
                        ,CASE WHEN FE.DATA_DATE='20170331'  THEN COALESCE(FE.RATE,0)        ELSE 0   END    AS RATE_SUM_AMT_LY         -- 去年同期发生额利率合计
                        ,CASE WHEN FE.DATA_DATE='20180331'       THEN 1                                   END    AS AMT_COUNT               -- 当月贷款发生额笔数
                        ,CASE WHEN FE.DATA_DATE='20180228' THEN 1                                   END    AS AMT_COUNT_LM            -- 上月贷款发生额笔数
                        ,CASE WHEN FE.DATA_DATE='20170331'  THEN 1                                   END    AS AMT_COUNT_LY            -- 去年同期贷款发生额笔数
                FROM EDW.DS_LOAB_SUM  YE
                LEFT JOIN (SELECT * FROM EDW.EV_LOAF_TRAN  WHERE DATA_DATE IN ('20180331' ,'20180228','20170331') )FE  -- 本月末,上月末,去年同期   没有卡贷款发放收回标志
                ON   YE.DUEBILL_NO=FE.DUEBILL_NO
                AND  YE.DATA_DATE=FE.DATA_DATE
                -- 与机构表最细级关联
                LEFT JOIN DIMENSION.T_ORG_BIZ_LVL ORG ON YE.FIN_ORG_NO=ORG.ORG_CODE_4 AND ORG.IS_ACTIVE= '1'AND '20180331' BETWEEN ORG.START_DATE AND ORG.END_DATE
                -- 与地区表最细级关联
                LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON YE.FIN_ORG_NO=REAL.ORG_ID AND '20180331' BETWEEN REAL.START_DATE AND REAL.END_DATE
                LEFT JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '20180331' BETWEEN AREA.START_DATE AND AREA.END_DATE
                -- 贷款产品类别
                LEFT JOIN DIMENSION.DIM_LOAN_PRODUCT PRO  ON YE.BUSINESS_TYPE=PRO.LOAN_PRODUCT_ID AND PRO.IS_VALID= '1' AND '20180331' BETWEEN PRO.START_DATE AND PRO.END_DATE
                -- 实际投向
                LEFT JOIN DIMENSION.DIM_INDUSTRY ACTU  ON YE.LOAN_INDUSTRY=ACTU.INDUSTRY_CODE_3  AND ACTU.IS_VALID= '1' AND '20180331' BETWEEN ACTU.START_DATE AND ACTU.END_DATE
                -- 企业出资人经济成分
                LEFT JOIN DIMENSION.DIM_ECONOMIC_SECTOR_CATAGORY ECO  ON YE.ECONOMY_TYPE=ECO.ECO_SEC_CAT_ID_3 AND ECO.IS_VALID= '1' AND '20180331' BETWEEN ECO.START_DATE AND ECO.END_DATE
                -- 担保方式
                LEFT JOIN DIMENSION.DIM_LOAN_GUARANTY_TYPE GUAR  ON YE.GUARANTY_TYPE=GUAR.LOAN_GUAR_TYPE_ID_2 AND GUAR.IS_VALID= '1' AND '20180331' BETWEEN GUAR.START_DATE AND GUAR.END_DATE
                WHERE YE.DATA_DATE IN ('20180331' ,'20180228','20170331') -- 本月末,上月末,去年同期
     ) T
    GROUP BY
             T.ORG_CODE_1
            ,T.ORG_DSCR_1
            ,T.ORG_CODE_2
            ,T.ORG_DSCR_2
            ,T.ORG_CODE_3
            ,T.ORG_DSCR_3
            ,T.AREA_CODE_1
            ,T.AREA_DSCR_1
            ,T.AREA_CODE_2
            ,T.AREA_DSCR_2
            ,T.AREA_CODE_3
            ,T.AREA_DSCR_3
            ,T.CUSTOMER_TYPE
            ,T.PRODUCT_CATEGORY
            ,T.LPIC
            ,T.LOANS_ACTUALLY
            ,T.ENTERPRISE_SCALE
            ,T.LOAN_PERIOD
            ,T.ENTERPRISE_INVESTOR
            ,T.GUARANTY_STYLE
            ,T.LOAN_QUALITY
            ,T.LOAN_STATUS
            ,T.CCY      ;